#!/usr/bin/python
# -*- coding: iso-8859-15 -*-

from xml.etree.ElementTree import parse
from xml.etree.ElementTree import ElementTree
from xml.etree.ElementTree import Element
import re
#from xml.dom.minidom import parse
from DBA import DBAccess
from Mapping.mapManager import Mapping
from datetime import datetime

class DBInterface:
    def __init__(self,filename="./DAC/DBA/dbaccess.config"):
        dom = parse(filename)
        root = dom.getroot()
        #DataBase connection
        self.dbname = root.find("dbname").text                 
        self.host   = root.find("host").text 
        self.port   = int(root.find("port").text)
        self.user   = root.find("user").text
        self.passwd = root.find("passwd").text
        #Tables Names
        self.iwconfigtablename = root.find("tablesName").find("iwconfig").text
        self.iwlisttablename = root.find("tablesName").find("iwlist").text 
        self.celluleIwlisttablename = root.find("tablesName").find("celluleIwlist").text
        self.campagnetablename = root.find("tablesName").find("campagne").text 
        self.mesuretablename = root.find("tablesName").find("mesure").text
        self.accessPointtablename = root.find("tablesName").find("accessPoint").text 
        self.salletablename = root.find("tablesName").find("salle").text
        self.batimenttablename = root.find("tablesName").find("batiment").text 
        self.iperftablename = root.find("tablesName").find("iperf").text
        self.ref_AP_Salle_Campagnetablename = root.find("tablesName").find("ref_AP_Salle_Campagne").text 
        self.ref_iperf_mesuretablename = root.find("tablesName").find("ref_iperf_mesure").text
        #Mapping FileName
        self.iwlistmapping = root.find("mappingFiles").find("iwlist").text
        self.iwconfigmapping = root.find("mappingFiles").find("iwconfig").text
        self.celluleIwlistmapping = root.find("mappingFiles").find("celluleIwlist").text
        self.campagnemapping = root.find("mappingFiles").find("campagne").text
        self.mesuremapping = root.find("mappingFiles").find("mesure").text
        self.accessPointmapping = root.find("mappingFiles").find("accessPoint").text
        self.sallemapping = root.find("mappingFiles").find("salle").text
        self.iperfmapping = root.find("mappingFiles").find("iperf").text
        self.batimentmapping = root.find("mappingFiles").find("batiment").text
        self.ref_iperf_mesuremapping = root.find("mappingFiles").find("iperfMesure").text
        self.ref_AP_Salle_CampagneMapping = root.find("mappingFiles").find("ref_AP_Salle_Campagne").text
    
    def __createDBA(self):
        return DBAccess(dbname=self.dbname, host=self.host, port=self.port, user =self.user,passwd=self.passwd)
    
    def testDBA(self):
        access=DBAccess(dbname=self.dbname, host=self.host, port=self.port, user =self.user,passwd=self.passwd)
        try:
            test=access.getTable()
        except AttributeError:
            return False
        else:
            return True

    def getTable(self):
        access=DBAccess(dbname=self.dbname, host=self.host, port=self.port, user =self.user,passwd=self.passwd)
        return access.getTable()

    def __createTuple(self,dico,mapping):
        keys = [mapping.getValue(x) for x in dico.keys()]
        values = dico.values()
        i = 0;
        d = {};
        for elem in keys:
            d[elem] = values[i]
            i=i+1
        return d

    def __createTupleInverse(self,dicoInverse,mapping):
        keys = [mapping.getKey(x) for x in dicoInverse.keys()]
        values = dicoInverse.values()
        i = 0;
        d = {};
        for elem in keys:
            d[elem] = values[i]
            i=i+1
        return d

    def createNewID(self):
        uid = ''
        date = datetime.today()
        uid = uid+str(date.year)+"-"+str(date.month)+"-"+str(date.day)+"-"+str(date.hour)+"-"+str(date.minute)+"-"+str(date.second)+"-"+str(date.microsecond)
        return uid;
    
    def insert(self,tableName,tablemapping,dicodata):
        dba = self.__createDBA()
        mapping = Mapping(tablemapping)
        dba.insert(tableName,self.__createTuple(dicodata,mapping))

    def update(self,tableName,tablemapping,dicodata):
        dba = self.__createDBA()
        mapping = Mapping(tablemapping)
        dba.update(tableName,self.__createTuple(dicodata,mapping))

    def deleteFromTable(self,tableName,tablemapping,clause={}):       
        dba = self.__createDBA()
        mapping = Mapping(tablemapping)
        query = "delete from "+str(tableName)+self.__getClause(clause,mapping)
        result = dba.executeQuery(query)
        return result

    def selectTable(self,tablename,tablemapping,clause={}):
        dba = self.__createDBA()
        mapping = Mapping(tablemapping)
        query = "select * from "+str(tablename)+self.__getClause(clause,mapping)
        result = dba.executeQuery(query).dictresult()
        table = []
        for row in result:
            table.append(self.__createTupleInverse(row,mapping))
        return table;

    def selectTableInverse(self,tablename,clause={}):
        dba = self.__createDBA()
        query = "select * from "+str(tablename)+self.__getClauseInverse(clause)
        result = dba.executeQuery(query).dictresult()
        table = []
        for row in result:
            table.append(row)
        return table;

    def __getClause(self,dico,mapping):
        clause = ''
        if dico:
            clause = clause + " Where "
            filtre = self.__createTuple(dico,mapping)
            has_new = 0;
            for key in filtre.keys():
                if has_new:
                    clause = clause+" and "
                clause = clause+str(key)+"= '" + str(filtre[key])+"'"
                has_new = has_new+1;
        return clause

    def __getClauseInverse(self,dico):
        clause = ""
        if dico:
            clause = clause+" Where "
            has_new = 0;
            for key in dico.keys():
                if has_new:
                    clause = clause+" and "
                clause = clause+str(key)+"= '" + str(dico[key])+"'"
                has_new = has_new+1;
        return clause

if __name__=="__main__":
    db = DBInterface("./dbaccess.config")
    db.exportDataToFile("/tmp/export.xml")
